﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using Persons.Classes;
using Persons.Interfaces;
using Persons.Attributes;
using System.Reflection;
using System.IO;

namespace Persons.DS
{
    /// <summary>
    /// Класс-наследник интерфейса фабрики для реализации работы с файлами БД
    /// </summary>
    public class DS_DB : IDS
    {
        private string path;
        public string Path { set { path = value; } }
        private IDS next = null;
        public IDS Next { set { next = value; } }
        public static OleDbConnection Connection;
        public DS_DB() { }
        public DS_DB(string _path)
        {
            Path = _path;
            OleDbConnectionStringBuilder stringBuilder = new OleDbConnectionStringBuilder { ConnectionString = path };
            DS_DB.Connection = new OleDbConnection(stringBuilder.ConnectionString);
        }

        public IDS isReady(string _ext, string _path)
        {
            IDS ret = null;
            if ("db" == _ext) { ret = this; ret.Path = _path; }
            else if (next != null) { ret = next.isReady(_ext, _path); }
            return ret;
        }

        public void Create(Persona p)
        {
            try
            {
                DS_DB.Connection.Open();
                OleDbCommand command = DS_DB.Connection.CreateCommand();
                command.CommandText = p.MakeDBStrFromPersona();
                OleDbParameter oleDbParameter = new OleDbParameter("Photo", OleDbType.VarBinary);
                oleDbParameter.Value = p.Photo;
                command.Parameters.Add(oleDbParameter);
                command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw new SystemException(ex.Message);
            }
            finally
            {
                DS_DB.Connection.Close();
            }
        }

        public List<Persona> Read()
        {
            try
            {
                List<Persona> list = new List<Persona>();
                DS_DB.Connection.Open();
                OleDbCommand command = DS_DB.Connection.CreateCommand();
                command.CommandText = "SELECT * FROM Persona";
                OleDbDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    Persona per = Persona.GetPersonaFromDB(reader);
                    list.Add(per);
                }
                return list;
            }
            catch (Exception ex)
            {
                throw new SystemException(ex.Message);
            }
            finally
            {
                DS_DB.Connection.Close();
            }
        }

        public List<Persona> Read(int ID)
        {
            try
            {
                List<Persona> list = new List<Persona>();
                DS_DB.Connection.Open();
                OleDbCommand command = DS_DB.Connection.CreateCommand();
                command.CommandText = "SELECT * FROM Persona WHERE Persona.ID =" + ID;
                OleDbDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    Persona per = Persona.GetPersonaFromDB(reader);
                    list.Add(per);
                }
                return list;
            }
            catch (Exception ex)
            {
                throw new SystemException(ex.Message);
            }
            finally
            {
                DS_DB.Connection.Close();
            }
        }

        public void Update(Persona per)
        {
            try
            {
                DS_DB.Connection.Open();
                OleDbCommand command = DS_DB.Connection.CreateCommand();
                if (per.Photo != null)
                {
                    command.CommandText = "UPDATE Persona SET Fname=\'" + per.Fname + "\'" + ",Lname=\'" + per.Lname + "\'" + ", Age=" + per.Age + ", Photo=? WHERE Persona.ID=" + per.ID;
                    OleDbParameter oleDbParameter = new OleDbParameter("Photo", OleDbType.VarBinary);
                    oleDbParameter.Value = per.Photo;
                    command.Parameters.Add(oleDbParameter);
                }
                else
                {
                    command.CommandText = "UPDATE Persona SET Fname=\'" + per.Fname + "\'" + ",Lname=\'" + per.Lname + "\'" + ", Age=" + per.Age + " WHERE Persona.ID=" + per.ID;
                }
                OleDbDataReader reader = command.ExecuteReader();
            }
            catch (Exception ex)
            {
                throw new SystemException(ex.Message);
            }
            finally
            {
                DS_DB.Connection.Close();
            }
        }

        public void Delete(int ID)
        {
            try
            {
                DS_DB.Connection.Open();
                OleDbCommand command = DS_DB.Connection.CreateCommand();
                command.CommandText = "DELETE FROM Persona WHERE Persona.ID=" + ID;
                OleDbDataReader reader = command.ExecuteReader();
            }
            catch (Exception ex)
            {
                throw new SystemException(ex.Message);
            }
            finally
            {
                DS_DB.Connection.Close();
            }
        }
    }
}
